package com.jzwl.xydk.manager.skillUser.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.jzwl.common.page.PageObject;
import com.jzwl.system.base.dao.BaseDAO;
import com.jzwl.xydk.manager.user.userBasicinfo.pojo.UserBasicinfo;
import com.jzwl.xydk.wap.business.home.pojo.UserSkillclassF;

@Repository("SkillUserManagerDao")
public class SkillUserManagerDao {
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：技能空间列表
	 * 创建人： ln
	 * 创建时间： 2016年10月13日
	 * 标记：manager
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public PageObject skillSpaceList(Map<String, Object> map) {
		try {
			
			String sql= "SELECT "
						+"	b.id,b.userName,b.cityId,b.schoolId,b.phoneNumber,b.email,b.wxNumber,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,si.schoolName"
						+" FROM "
						+	" `xiaoka-xydk`.user_basicinfo b "
						+" LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
						+" LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId " 
						+" where b.isDelete = 0 ";
			if(null !=map.get("userName") && StringUtils.isNotEmpty(map.get("userName").toString())){
		  		sql=sql+ " and b.userName like '%" + map.get("userName") +"%'";
		  	}
			if(null !=map.get("isRecommend") && StringUtils.isNotEmpty(map.get("isRecommend").toString())){
		  		sql=sql+ " and b.isRecommend  = '" + map.get("isRecommend") +"'";
		  	}
			if(null !=map.get("skillStatus") && StringUtils.isNotEmpty(map.get("skillStatus").toString())){
		  		sql=sql+ " and b.skillStatus  = '" + map.get("skillStatus") +"'";
		  	}
		  	if(null !=map.get("auditState") && StringUtils.isNotEmpty(map.get("auditState").toString())){
		  		sql=sql+ " and b.auditState  = '" + map.get("auditState") +"'";
		  	}
			sql=sql+ " order by b.createDate desc ";
			PageObject po = baseDAO.queryForMPageList(sql, new Object[]{},map);
			return po;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：根据技能空间Id获取技能空间基本信息
	 * 创建人： ln
	 * 创建时间： 2016年10月13日
	 * 标记：manager
	 * @map map
	 * @return
	 * @version
	 */
	public Map<String, Object> getSkillBaseInfo(Map<String, Object> map) {
		try {
			String sql= "SELECT "
					+"	b.id,b.userName,b.cityId,b.schoolId,b.openId,b.phoneNumber,b.email,b.wxNumber,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,si.schoolName"
					+" FROM "
					+	"`xiaoka-xydk`.user_basicinfo b "
					+" LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
					+" LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
					+ " where b.isDelete = 0 and b.id = "+map.get("id")+"" ;
			return baseDAO.queryForMap(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：根据技能空间获取空间内技能信息
	 * 创建人： ln
	 * 创建时间： 2016年10月13日
	 * 标记：manager
	 * @map paramsMap
	 * @return
	 * @version
	 */
	public List<Map<String, Object>> querySkillByUserId(
			Map<String, Object> map) {
		try {
			String sql = " SELECT "
						+" 	usi.id,usi.basicId,usi.skillName,usi.textUrl,usi.videoURL,usi.skillPrice,usi.serviceType,usi.`skillDepict`,usi.createDate,usf.className as fatName,uss.className as sonName " 
						+" FROM " 
						+	" `xiaoka-xydk`.user_skillinfo usi "  
						+" LEFT JOIN `xiaoka-xydk`.user_skillclass_father usf ON usi.skillFatId = usf.id "
						+" LEFT JOIN `xiaoka-xydk`.user_skillclass_son uss on usi.skillSonId = uss.id "
						+ " where usi.isDelete = 0 and usi.basicId = "+map.get("id")+"";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：设置推荐或不推荐
	 * 创建人： ln
	 * 创建时间： 2016年10月13日
	 * 标记：
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public boolean updateSkillSpaceRecommend(Map<String, Object> map) {
		try {
			String sql =" update `xiaoka-xydk`.user_basicinfo set "
					+ " isRecommend=:isRecommend "
					+ " where id=:id";
			return  baseDAO.executeNamedCommand(sql, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：设置个人空间打开或关闭
	 * 创建人： ln
	 * 创建时间： 2016年10月13日
	 * 标记：manager
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public boolean skillSpaceCloseOrOpen(Map<String, Object> map) {
		try {

			String sql =" update `xiaoka-xydk`.user_basicinfo set "
					+ " skillStatus=:skillStatus "
					+ " where id=:id";
			return  baseDAO.executeNamedCommand(sql, map);			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：个人空间审核
	 * 创建人： ln
	 * 创建时间： 2016年10月13日
	 * 标记：
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public boolean skillSpaceCheck(Map<String, Object> map) {
		try {
			String sql =" update `xiaoka-xydk`.user_basicinfo set "
					+ " auditState=:auditState "
					+ " where id=:id";
			return  baseDAO.executeNamedCommand(sql,map);	
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：根据技能IDs获取技能图片
	 * 创建人： ln
	 * 创建时间： 2016年10月13日
	 * 标记：
	 * @param imgIds
	 * @return
	 * @version
	 */
	public List<Map<String, Object>> queryImgBySkillIds(String imgIds) {
		try {
			String sql = "SELECT skillId,imgURL from `xiaoka-xydk`.user_skill_image  where skillId IN ("+imgIds+") and imgType = 1";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public String getColumns() {
		return ""
				+" t.id as id,"
				+" t.userName as userName,"
				+" t.cityId as cityId,"
				+" c.cityName as cityName,"
				+" t.schoolId as schoolId,"
				+" si.schoolName as schoolName,"
				+" t.phoneNumber as phoneNumber,"
				+" t.email as email,"
				+" t.wxNumber as wxNumber,"
				+" t.openId as openId,"
				+" t.aboutMe as aboutMe,"
				+" t.auditState as auditState,"
				+" t.auditDate as auditDate,"
				+" t.auditUser as auditUser,"
				+" t.auditNotes as auditNotes,"
				+" t.viewNum as viewNum,"
				+" t.createDate as createDate,"
				+" t.createUser as createUser,"
				+" t.isRecommend as isRecommend,"
				+" t.skillStatus as skillStatus,"
				+" t.isDelete as isDelete"
				;
	}
	
	
	public List<UserBasicinfo> getUserBasicinfos(Map<String,Object> map){
		
		String sql="select " + getColumns() + " from `xiaoka-xydk`.user_basicinfo t "
				+" LEFT JOIN `xiaoka`.tg_city c ON t.cityId = c.id "
				+" LEFT JOIN `xiaoka`.tg_school_info si ON si.id = t.schoolId "
				+" where t.isDelete = 0 ";
		
		
		if(null !=map.get("userName") && StringUtils.isNotEmpty(map.get("userName").toString())){
	  		sql=sql+ " and t.userName like '%" + map.get("userName") +"%'";
	  	}
		if(null !=map.get("isRecommend") && StringUtils.isNotEmpty(map.get("isRecommend").toString())){
	  		sql=sql+ " and t.isRecommend  = '" + map.get("isRecommend") +"'";
	  	}
		if(null !=map.get("skillStatus") && StringUtils.isNotEmpty(map.get("skillStatus").toString())){
	  		sql=sql+ " and t.skillStatus  = '" + map.get("skillStatus") +"'";
	  	}
	  	if(null !=map.get("auditState") && StringUtils.isNotEmpty(map.get("auditState").toString())){
	  		sql=sql+ " and t.auditState  = '" + map.get("auditState") +"'";
	  	}
		
		List<UserBasicinfo> basicinfos = baseDAO.getJdbcTemplate().query(
				sql,
				new Object[] {},
				ParameterizedBeanPropertyRowMapper
						.newInstance(UserBasicinfo.class));
		return basicinfos;
	}
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：修改用户基本信息
	 * 创建人： ln
	 * 创建时间： 2016年11月2日
	 * 标记：manager
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public boolean editBaseInfo(Map<String, Object> map) {
		try {
			String sql =" update `xiaoka-xydk`.user_basicinfo set "
					+ " userName=:userName,phoneNumber=:phoneNumber,email=:email,wxNumber=:wxNumber,aboutMe=:aboutMe "
					+ " where id=:id";
			return  baseDAO.executeNamedCommand(sql, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：根据技能IDs获取技能图片
	 * 创建人： dxf
	 * 创建时间： 2016年2月16日
	 * 标记：
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public List<Map<String, Object>> toEditLabel(Map<String, Object> paramsMap) {
		try {
			String sql = "SELECT dt.id dataId,dt.dic_id dicId,dt.dic_name,dt.dic_value,dt.isDelete "
					+ " FROM v2_dic_data dt INNER JOIN v2_dic_type tp ON dt.dic_id=tp.dic_id "
					+ " WHERE tp.dic_code='ResourceLabel' AND dt.isDelete=0 AND tp.isDelete=0"; 
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：查询已选标签
	 * 创建人： dxf
	 * 创建时间： 2016年2月16日
	 * 标记：daskil.id,dcda.id dicId,skil.id skiId,dcda.dic_id,dcda.dic_name,dcda.dic_value
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public List<Map<String, Object>> querySkilDicdata(Map<String, Object> paramsMap) {
		try {
			String sql = "SELECT dcda.id dicId FROM "
					+ "`xiaoka-xydk`.dicdata_skillinfo daskil INNER JOIN `xiaoka-xydk`.user_basicinfo skil ON daskil.skilinfoId=skil.id"
					+" INNER JOIN v2_dic_data dcda ON dcda.id=daskil.dicDataId WHERE skil.id='"+paramsMap.get("id")+"' ";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 
	 * 项目名称：dlws-xiaoka-xydk
	 * 描述：执行修改资源所属标签
	 * 创建人： dxf
	 * 创建时间： 2016年2月16日
	 * 标记：daskil.id,dcda.id dicId,skil.id skiId,dcda.dic_id,dcda.dic_name,dcda.dic_value
	 * @param paramsMap
	 * @return
	 * @version
	 */
	public boolean updateLabel(Map<String, Object> map) {
		Date date = new Date();
		map.put("createTime", date);
		String yxId=(String) map.get("yxId");
		String yxIdArr[] = yxId.split(",");
		
			String sql = "INSERT INTO `xiaoka-xydk`.dicData_Skillinfo(dicDataId,skilinfoId,isDelete,createTime) "
					+ "VALUES (:dicId,:id,0,:createTime)";
			
			//先执行删除再添加
			String delSql ="DELETE FROM `xiaoka-xydk`.dicdata_skillinfo  WHERE skilinfoId IN('"+map.get("id")+"')";
			baseDAO.executeNamedCommand(delSql, map);
			
					
			for(int i=0;i<yxIdArr.length;i++){
				if(yxIdArr[i]!=""&&yxIdArr[i]!=null){
					String dicId = yxIdArr[i];
					map.put("dicId", dicId);
					baseDAO.executeNamedCommand(sql, map);
				}
			}
			
			return true;
	}
	
	
	
	public List<Map<String, Object>> queryExit(Map<String, Object> paramsMap) {
		try {
			String sql = "SELECT id FROM `xiaoka-xydk`.dicdata_skillinfo "
			 +" WHERE skilinfoId='"+paramsMap.get("id")+"' ";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
}
/*if(null !=map.get("skillStatus") && StringUtils.isNotEmpty(map.get("skillStatus").toString())){
	sql=sql+ " and DATE_FORMAT(a.createTime,'%Y-%m-%d')  >= DATE_FORMAT('"+map.get("beginTime")+"','%Y-%m-%d')";
}
if(null !=map.get("endTime") && StringUtils.isNotEmpty(map.get("endTime").toString())){
	sql=sql+ " and DATE_FORMAT(a.createTime,'%Y-%m-%d')  <= DATE_FORMAT('"+map.get("endTime")+"','%Y-%m-%d')";
}*/
